﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using ww.wwf.wwfbll;
using System.Text.RegularExpressions;
namespace ww.form.wwf
{
    public partial class UserdefinedTypeBaseForm : SysBaseForm
    {
        ExcelHelper excelhelp = new ExcelHelper("", "");
        /// <summary>
        /// 表ID
        /// </summary>
        private string strTable_id = "";
        /// <summary>
        /// 原始值 数据表
        /// </summary>
        private DataTable dtYSValue = null;
        /// <summary>
        /// Excel导出模板
        /// </summary>
        private string strExcelTemplate = "";
        /// <summary>
        /// 据表ID取出的列值
        /// </summary>
        private DataTable dtColumns = null;
        /// <summary>
        /// 据表ID取出的表
        /// </summary>
        private DataTable dtTable = null;

        /// <summary>
        /// 自定义业务规则
        /// </summary>
        private UserdefinedBLL bll = new UserdefinedBLL();
        /// <summary>
        /// 当前要建临时表
        /// </summary>
        private string dbCreatTemTableName = "";
        /// <summary>
        /// 取出临时表的值
        /// </summary>
        public DataTable dtDBTemTable = new DataTable();
        public UserdefinedTypeBaseForm()
        {
            InitializeComponent();
        }

        private void UserdefinedTypeBaseForm_Load(object sender, EventArgs e)
        {
            //dataGridViewShow.DataSource = this.dtYSValue;
        }

        protected void ShowValue()
        {
            try
            {
                this.dtTable = new DataTable();
                this.dtColumns = new DataTable();
                dtTable = this.bll.BllTypeDTByftable_id(strTable_id);

                if (dtTable.Rows.Count > 0)
                {
                    string ftype = dtTable.Rows[0]["ftype"].ToString();//表类型 只表才做运行
                    this.strExcelTemplate = dtTable.Rows[0]["ftemplate"].ToString();//导出模板
                    int fsum_flag = Convert.ToInt32(dtTable.Rows[0]["fsum_flag"].ToString());//有合计否
                    if (ftype == "表")
                    {
                        this.dtColumns = this.bll.BllColumnsByftable_id(strTable_id);
                        if (this.dtColumns.Rows.Count > 0)
                        {
                            #region 创建数据临时表 并取出公式值
                            this.dbCreatTemTableName = "tem_" + this.bll.DbGuid();
                            //string sqlCreate = "create table " + dbCreatTemTableName + "(pk_accsubj char(20),subjcode varchar(30),subjname varchar(40),dispname varchar(200),unit varchar(10))";
                            //string sqlCreate = "create table " + dbCreatTemTableName + " (fid,";//要创建的列
                            StringBuilder sqlCreate = new StringBuilder();//创建sql
                            StringBuilder sqlInsertCol = new StringBuilder();//插入的列

                            StringBuilder sqlGetGS = new StringBuilder();//取得公式
                            sqlInsertCol.Append("INSERT INTO " + dbCreatTemTableName + "  (fid");


                            //sqlCreate.Append("create table " + dbCreatTemTableName + " (fid varchar(32)");
                            if (LoginBLL.sysDB == "Access")
                                sqlCreate.Append("create table " + dbCreatTemTableName + " (fid " + ww.wwf.dao.DBDataType.strVarchar_Access + "(32)");
                            else if (LoginBLL.sysDB == "SqlServer")
                                sqlCreate.Append("create table " + dbCreatTemTableName + " (fid " + ww.wwf.dao.DBDataType.strVarchar_SqlServer + "(32)");
                            else
                                sqlCreate.Append("create table " + dbCreatTemTableName + " (fid " + ww.wwf.dao.DBDataType.strVarchar_Oracle + "(32)");

                            string strColumnName = "";//列名
                            string strfvalue_type = "";//值类型
                            string strfvalue_content = "";//公式值
                            string strfdata_type = "";//数据类型
                            //MessageBox.Show("dd");
                            for (int i = 0; i < dtColumns.Rows.Count; i++)
                            {
                                strfvalue_type = dtColumns.Rows[i]["fvalue_type"].ToString();
                                strfvalue_content = dtColumns.Rows[i]["fvalue_content"].ToString();
                                strColumnName = dtColumns.Rows[i]["fcode"].ToString();
                                strfdata_type = dtColumns.Rows[i]["fdata_type"].ToString();
                                if (strfvalue_type == "公式值")
                                {
                                    sqlGetGS.Append(",(" + strfvalue_content + ") as " + strColumnName);
                                }
                                else
                                {
                                    if (strfdata_type == "数字型")
                                    {
                                        //sqlCreate.Append("," + strColumnName + " float(8)");

                                    if (LoginBLL.sysDB == "Access")
                                        sqlCreate.Append("," + strColumnName + " " + ww.wwf.dao.DBDataType.strFloat_Access);
                                    else if (LoginBLL.sysDB == "SqlServer")
                                        sqlCreate.Append("," + strColumnName + " " + ww.wwf.dao.DBDataType.strFloat_SqlServer);
                                    else
                                        sqlCreate.Append("," + strColumnName + " " + ww.wwf.dao.DBDataType.strFloat_Oracle);
                                    }
                                    else
                                    {
                                        //sqlCreate.Append("," + strColumnName + " varchar(100)"); 

                                        if (LoginBLL.sysDB == "Access")
                                            sqlCreate.Append("," + strColumnName + " " + ww.wwf.dao.DBDataType.strVarchar_Access + "(100)");
                                        else if (LoginBLL.sysDB == "SqlServer")
                                            sqlCreate.Append("," + strColumnName + " " + ww.wwf.dao.DBDataType.strVarchar_SqlServer + "(100)");
                                        else
                                            sqlCreate.Append("," + strColumnName + " " + ww.wwf.dao.DBDataType.strVarchar_Oracle + "(100)"); 
                                    }
                                    sqlInsertCol.Append("," + strColumnName);
                                }
                            }

                            sqlCreate.Append(")");
                            //string sql = "SELECT * " + sqlGetGS.ToString() + " FROM " + this.dbCreatTemTableName + " order by fid";
                            string sql = "SELECT * " + sqlGetGS.ToString() + " FROM " + this.dbCreatTemTableName + " order by fid";
                            this.bll.BllTemTableCreate(sqlCreate.ToString());//临时表创建

                            #endregion

                            #region //给当前表设置值
                            CurrDTSetValue(sqlInsertCol.ToString());
                            #endregion

                            dtDBTemTable = this.bll.BllTemTableDT(sql); //取出临时表的值                    
                            CurrDTSum(fsum_flag);// 求和                           
                            this.dataGridViewShow.DataSource = dtDBTemTable;
                            CurrGridViewSet();//当前GridView设置

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ww.wwf.wwfbll.WWMessage.MessageShowError(ex.ToString());
            }
            finally
            {
                try
                {
                    this.bll.BllTemTableDel(this.dbCreatTemTableName);//临时表删除
                }
                catch { }
            }
        }
        /// <summary>
        /// 给当前表设置值
        /// </summary>
        /// <param name="sqlInsertCol"></param>
        private void CurrDTSetValue(string sqlInsertCol)
        {
            IList sqlList = new ArrayList();
            for (int intYS = 0; intYS < dtYSValue.Rows.Count; intYS++)
            {
                StringBuilder sqlInsertColValue = new StringBuilder();//插入的列 值
                string strvalue = null;
                for (int intYSC = 0; intYSC < dtYSValue.Columns.Count; intYSC++)
                {

                    strvalue = dtYSValue.Rows[intYS][intYSC].ToString();
                    if (intYSC == 0)
                    {
                        sqlInsertColValue.Append("'" + strvalue + "'");
                    }
                    else
                    {
                        if (IsNumber(strvalue))
                        {
                            if (Convert.ToDouble(strvalue) == 0)
                                sqlInsertColValue.Append(",null");
                            else
                                sqlInsertColValue.Append("," + strvalue + "");
                        }
                        else
                        {
                            if (strvalue == null || strvalue == "")
                                sqlInsertColValue.Append(",null");
                            else
                                sqlInsertColValue.Append(",'" + strvalue + "'");
                        }
                    }
                }
                string sqlInsert = sqlInsertCol + ") VALUES (" + sqlInsertColValue.ToString() + ")";
                sqlList.Add(sqlInsert);

            }
            this.bll.BllTemTableInsertBusiness(sqlList);
        }
        public bool IsNumber(String strNumber)
        {

            Regex objNotNumberPattern = new Regex("[^0-9.-]");
            Regex objTwoDotPattern = new Regex("[0-9]*[.][0-9]*[.][0-9]*");
            Regex objTwoMinusPattern = new Regex("[0-9]*[-][0-9]*[-][0-9]*");
            String strValidRealPattern = "^([-]|[.]|[-.]|[0-9])[0-9]*[.]*[0-9]+$";
            String strValidIntegerPattern = "^([-]|[0-9])[0-9]*$";
            Regex objNumberPattern = new Regex("(" + strValidRealPattern + ")|(" + strValidIntegerPattern + ")");

            return !objNotNumberPattern.IsMatch(strNumber) &&
            !objTwoDotPattern.IsMatch(strNumber) &&
            !objTwoMinusPattern.IsMatch(strNumber) &&
            objNumberPattern.IsMatch(strNumber);
        }   
        /*
        /// <summary>
        /// 给当前表设置值
        /// </summary>
        /// <param name="sqlInsertCol"></param>
        private void CurrDTSetValue(string sqlInsertCol)
       {
           IList sqlList = new ArrayList();
           for (int intYS = 0; intYS < dtYSValue.Rows.Count; intYS++)
           {
               StringBuilder sqlInsertColValue = new StringBuilder();//插入的列 值
               for (int intYSC = 0; intYSC < dtYSValue.Columns.Count; intYSC++)
               {
                   if (intYSC == 0)
                       sqlInsertColValue.Append("'" + dtYSValue.Rows[intYS][intYSC].ToString() + "'");
                   else
                       sqlInsertColValue.Append(",'" + dtYSValue.Rows[intYS][intYSC].ToString() + "'");

               }
               string sqlInsert = sqlInsertCol + ") VALUES (" + sqlInsertColValue.ToString() + ")";
               sqlList.Add(sqlInsert);

           }
           this.bll.BllTemTableInsert(sqlList);
       }*/

        /// <summary>
        /// 当前表Sum
        /// </summary>
        /// <param name="fsum_flag"></param>
        private void CurrDTSum(int fsum_flag)
        {
            if (fsum_flag == 1)
            {
                //计算合计                               
                string strCurrColumn = "";//当前列名
                string strfsum_flag = "0";//求和否
                string strfxsws = "0";//小数位数
                DataRow drRowSum = dtDBTemTable.NewRow();
                //drRowSum[1] = "合  计";
                for (int c = 0; c < dtDBTemTable.Columns.Count; c++)
                {
                    strCurrColumn = dtDBTemTable.Columns[c].ColumnName.ToString();

                    //fcode
                    DataRow[] currcolumXS = dtColumns.Select("fcode='" + strCurrColumn + "'");
                    if (currcolumXS != null)
                    {
                        foreach (DataRow drXS in currcolumXS)
                        {
                            strfsum_flag = drXS["fsum_flag"].ToString();
                            strfxsws = drXS["fxsws"].ToString();
                        }
                    }
                    if (strfsum_flag == "1")
                    {
                        if (strfxsws == "" || strfxsws == null)
                            strfxsws = "2";
                        Decimal decSumValue = 0;
                        for (int isum = 0; isum < dtDBTemTable.Rows.Count; isum++)
                        {
                            if (dtDBTemTable.Rows[isum][strCurrColumn].ToString() == "" || dtDBTemTable.Rows[isum][strCurrColumn].ToString() == null)
                            {
                                decSumValue = decSumValue + 0;
                            }
                            else
                            {
                               // dtDBTemTable.Rows[isum][strCurrColumn] = ww.wwf.com.Public.BllGetXSWS(Convert.ToDecimal(dtDBTemTable.Rows[isum][strCurrColumn].ToString()), Convert.ToInt32(strfxsws));  
                                dtDBTemTable.Rows[isum][strCurrColumn] = dtDBTemTable.Rows[isum][strCurrColumn].ToString(); 
                                decSumValue = decSumValue + Convert.ToDecimal(dtDBTemTable.Rows[isum][strCurrColumn].ToString());
                            }
                        }
                        //MessageBox.Show(decSumValue.ToString());
                       // decSumValue = ww.wwf.com.Public.BllGetXSWS(decSumValue, Convert.ToInt32(strfxsws));
                        //decSumValue = decSumValue;
                        // if (decSumValue != 0)
                        drRowSum[strCurrColumn] = decSumValue;
                    }
                }
                dtDBTemTable.Rows.Add(drRowSum);
            }
        }

        /// <summary>
        /// 当前GridView设置
        /// </summary>
        private void CurrGridViewSet()
        {
            try
            {

                for (int i = 0; i < this.dataGridViewShow.ColumnCount; i++)
                {
                    string strCurrColName = this.dataGridViewShow.Columns[i].Name.ToString();
                    DataRow[] colList = dtColumns.Select("fcode='" + strCurrColName + "'");
                    int fshow_width = 100;//宽度
                    int fshow_flag = 1;//显示标识               
                    string fname = "";//列名
                    this.dataGridViewShow.Columns[strCurrColName].Visible = false;
                    int fxsws = 2;//小数位数
                    string fdata_type = "";//数据类型
                    if (colList.Length > 0)
                    {
                        foreach (DataRow drC in colList)
                        {
                            try
                            {
                                fxsws = Convert.ToInt32(drC["fxsws"].ToString());
                            }
                            catch { fxsws = 2; }
                            fdata_type = drC["fdata_type"].ToString();
                            //MessageBox.Show(fdata_type);

                            //System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle1 = new System.Windows.Forms.DataGridViewCellStyle();
                            //dataGridViewCellStyle1.Format = "N2";
                            //dataGridViewCellStyle1.NullValue = null;

                            if (drC["fshow_width"].ToString() == "" || drC["fshow_width"].ToString() == null)
                            {
                            }
                            else
                            {
                                fshow_width = Convert.ToInt32(drC["fshow_width"].ToString());
                            }
                            if (drC["fshow_flag"].ToString() == "" || drC["fshow_flag"].ToString() == null)
                            {
                            }
                            else
                            {
                                fshow_flag = Convert.ToInt32(drC["fshow_flag"].ToString());
                            }
                            fname = drC["fname"].ToString();
                        }

                        if (fshow_flag == 1)
                        {
                            this.dataGridViewShow.Columns[strCurrColName].Visible = true;
                            this.dataGridViewShow.Columns[strCurrColName].Width = fshow_width;
                            this.dataGridViewShow.Columns[strCurrColName].HeaderText = fname;

                            if (fdata_type == "数字型")
                            {
                                this.dataGridViewShow.Columns[strCurrColName].DefaultCellStyle.Format = "N" + fxsws.ToString();

                            }
                            //this.dataGridViewShow.Columns[strCurrColName].DefaultCellStyle.NullValue = null;
                            // dataGridViewCellStyle1.NullValue = "null";
                        }
                        //this.dataGridViewShow.Columns["fid"].Visible = false;
                    }
                }
                // this.dataGridViewShow.Columns["fid"].Visible = false;
            }
            catch (Exception ex)
            {
                WWMessage.MessageShowError(ex.ToString());
            }
        }


        /// <summary>
        /// 返回表ID
        /// </summary>
        /// <param name="s"></param>
        private void EventResultChangedString(string s)
        {
            this.strTable_id = s;           
        }
        /// <summary>
        /// 返回数据表
        /// </summary>
        /// <param name="dt"></param>
        private void EventResultChangedDataTable(DataTable dt)
        {
            this.dtYSValue = dt;
           // this.dataGridViewShow.DataSource = this.dtYSValue;
        }

        private void buttonSet_Click(object sender, EventArgs e)
        {
            BllSet();
        }

        private void 列表设置ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            BllSet();
        }

        private void toolStripMenuItem1_Click(object sender, EventArgs e)
        {
            BllExcelOut();
        }

        private void butExcel_Click(object sender, EventArgs e)
        {
            BllExcelOut();
        }
        /// <summary>
        /// 列设置
        /// </summary>
        private void BllSet()
        {
            try
            {
                UserdefinedFieldUpdateForm userf = new UserdefinedFieldUpdateForm(this.strTable_id);
                userf.ShowDialog();
            }
            catch (Exception ex)
            {
                ww.wwf.wwfbll.WWMessage.MessageShowError(ex.ToString());
            }
        }
        /// <summary>
        /// 导出Excel
        /// </summary>
        private void BllExcelOut()
        {
            try
            {
                this.Validate();
                this.dataGridViewShow.EndEdit();
                if (this.dataGridViewShow.Rows.Count > 0)
                {
                    int intTop = 0;
                    int intLeft = 0;
                    try
                    {
                        intTop = Convert.ToInt32(textBoxTop.Text.Trim());
                    }
                    catch { }
                    try
                    {
                        intLeft = Convert.ToInt32(textBoxLeft.Text.Trim());
                    }
                    catch { }
                    this.excelhelp.GridViewToExcel(this.dataGridViewShow, this.strExcelTemplate, intTop, intLeft);
                }
                else {
                    ww.wwf.wwfbll.WWMessage.MessageShowWarning("暂无记录,不可导出!");
                }
            }
            catch (Exception ex)
            {
                ww.wwf.wwfbll.WWMessage.MessageShowError(ex.ToString());
            }
        }

        /// <summary>
        /// 查询窗口
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void butQuery_Click(object sender, EventArgs e)
        {
            Query();
        }
        private void Query()
        {
            try
            {
                this.strTable_id = "";
                this.dtYSValue = null;

                UserdefinedResult r = new UserdefinedResult();
                r.TextChangedString += new TextChangedHandlerString(this.EventResultChangedString);
                r.TextChangedDataTable += new TextChangedHandlerDataTable(this.EventResultChangedDataTable);
                UserdefinedQueryDemoForm fc = new UserdefinedQueryDemoForm(r);
                fc.ShowDialog();

                if (this.dtYSValue == null || this.dtYSValue.Rows.Count <= 0)
                {
                    dataGridViewShow.DataSource = null;
                }
                else
                {
                    ShowValue();
                }
                
            }
            catch (Exception ex)
            {
                ww.wwf.wwfbll.WWMessage.MessageShowError(ex.ToString());
            }
        }
        private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void toolStripMenuItem2_Click(object sender, EventArgs e)
        {
            Query();
        }

    }
}